package com.dbf;

import java.io.UnsupportedEncodingException;
import java.nio.charset.Charset;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Statement;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Iterator;
import java.util.Map;
import java.util.Properties;
import java.util.concurrent.ConcurrentSkipListMap;

public class TestDbf {
	private static Connection con=null;
	private static final String jdbURL = "jdbc:dbf:/./?caseInsensitive=true";
	private static Properties props = new Properties();
	static {
		try {
			Class.forName("com.caigen.sql.dbf.DBFDriver");
			props.setProperty("delayedClose", "0");
			props.setProperty("charset", "gb2312");
		} catch (ClassNotFoundException e) {
			e.printStackTrace();
		} 
	}
	public Statement getStatement(Connection con){
		Statement st=null;
		try {
			st= con.createStatement();
			//ResultSet.TYPE_SCROLL_SENSITIVE, ResultSet.CONCUR_UPDATABLE
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return st;
	}
	public Connection getCon() {
		Connection con=null;
		try {
			con = DriverManager.getConnection(jdbURL, props);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}
	public Connection getCon(String dir) {
		Connection con=null;
		try {
			String jdbURL = "jdbc:dbf:/"+dir+"./?caseInsensitive=true";
			con = DriverManager.getConnection(jdbURL, props);
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return con;
	}

	public void createDBF(Statement st,int column) throws SQLException{
		StringBuilder sb=new StringBuilder();
		for(int i=0;i<column;i++){
			sb.append("name").append(i).append(" varchar(25),");
		}
		sb.insert(0,"create table if not exists test(").deleteCharAt(sb.length()-1).append(")");
		st.executeUpdate("drop table if exists test");		
		st.executeUpdate(sb.toString());			
	}
	public int insert(Statement st,int column,int row) throws SQLException{
		StringBuilder sb=new StringBuilder();
		for(int i=0;i<row;i++){
			StringBuilder sb2=new StringBuilder();
			for(int j=0;j<column;j++){
				sb2.append("'value").append((int)(Math.random()*100)).append("',");
			}
			sb2.insert(0,"(").deleteCharAt(sb2.length()-1).append(")");
			sb.append(sb2).append(",");
		}
		sb.insert(0, "insert into test values ").deleteCharAt(sb.length()-1);
		//System.out.println("sb:"+sb);
		return st.executeUpdate(sb.toString());
	}
	
	public long test1(Statement st) throws SQLException{
		long start=System.currentTimeMillis();
		String sql="select distinct(name3), count(name3) p from test where 1=1 group by name3 having count(name3)>1";
		sql=" SELECT name3,COUNT(name3) FROM test WHERE name3 IN (SELECT DISTINCT(name3) FROM test ) GROUP BY name3 order by name3";
		ResultSet rs =st.executeQuery(sql);
		StringBuffer sb=new StringBuffer();
		while(null!=rs && rs.next() && rs.getInt(2)>1){
			sb.append(rs.getString(1)).append("(").append(rs.getInt(2)).append("处重复),");
		}
		if(sb.length()>1)
			sb.deleteCharAt(sb.length()-1);
		System.out.println("test1：数据包中有重复病案号P3：["+sb.toString()+"]");
		long end=System.currentTimeMillis();		
		return end-start;
	}
	public long test3(Statement st) throws SQLException{
		long start=System.currentTimeMillis();
		ResultSet rs =st.executeQuery("select * from test ");
		long end=System.currentTimeMillis();		
		return end-start;
	}
	public long test2(Statement st)throws SQLException{
		long start=System.currentTimeMillis();		
		ResultSet rs=st.executeQuery("select name3 from test order by name3");
		
		long middle=System.currentTimeMillis();
		System.out.println("time-m:"+(middle-start));
		Map<String , Integer>map=new ConcurrentSkipListMap<String ,Integer>();
		int count=0,total=0;
		while(null!=rs && rs.next()){
			//total=rs.getInt(1);
			String key=rs.getString(1);
			count++;
			if(count==999)
				System.out.println(count);
			if(null!=key && !key.trim().equals("")){
				if(map.containsKey(key)){
					map.put(key, map.get(key)+1);
				}else{
					map.put(key, 1);
				}
			}
		}
		long end=System.currentTimeMillis();
		Iterator<String >it=map.keySet().iterator();
		//java.util.List<String> list=new ArrayList<String>();
		StringBuilder sb=new StringBuilder();
		while(null!=it && it.hasNext()){
			String key=it.next();
			if(map.get(key)>1)
				sb.append(key).append("(").append(map.get(key)).append("处重复),");			
		}
		/*String[]a=list.toArray(new String[0]);
		Arrays.sort(a);
		list=Arrays.asList(a);
		for(String key:list){			
		}*/
		if(sb.length()>1)
			sb.deleteCharAt(sb.length()-1);
		System.out.println("test2：(count="+count+",total="+total+")数据包中有重复病案号P3：["+sb.toString()+"]");
		rs=st.executeQuery("select count(0) from test");
		if(null!=rs && rs.next()) 
			System.out.println("selectCount="+rs.getInt(1));
		rs.close();
		
		return end-start;
		
	}
	public void test4(Statement st,String value)throws SQLException{
		ResultSet rs=st.executeQuery("select count(0) from test where name3='"+value+"'");
		if(rs!=null && rs.next())
			System.out.println(value+"="+rs.getInt(1));
	}
	public void testResultSet(Statement st) throws SQLException{
		ResultSet rs=st.executeQuery("select * from test ");
		if(rs!=null && rs.last()){
			rs.previous();
			rs.first();
			int i=rs.getRow();
			System.out.println("rowId="+i);
		}
	}
	public void readDBF(Statement st) throws SQLException, UnsupportedEncodingException{
		ResultSet rs=st.executeQuery("select * from c0_88.dbf");
		for(int i=1;rs!=null && rs.next() ;i++ ){
			//System.out.println("p900="+rs.getString("p900"));			
			//!new String(rs.getString(1).getBytes("UTF-8")).equals(new String("而这一疆域内的文化恰恰".getBytes("UTF-8"))) ||
			if(  !rs.getString(2).equals("1") || !rs.getString("p5").equals("2") ){
				System.out.println("i="+i+","+rs.getString(1)+","+rs.getString(2)+","+rs.getString("p5"));
			}
		}
	}
	public static void main(String[] args) throws SQLException, UnsupportedEncodingException {
		TestDbf tb=new TestDbf();
		Connection con=tb.getCon();
		Statement st=tb.getStatement(con);
		/*int column=200;
		int row=5000;
		tb.createDBF(st,column);
		System.out.println("tb.insert:"+tb.insert(st,column,row));
		System.out.println("test1:"+tb.test1(st));
		System.out.println("test2:"+tb.test2(st));*/
		//System.out.println("test3:"+tb.test3(st));
		/*st=tb.getCon().createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
		tb.testResultSet(st);
		tb.test4(st,"value0");
		if(st!=null)
			st.close();
		if(con!=null)
			con.close();	*/
		Statement statement=tb.getCon("E:/").createStatement();
		tb.readDBF(statement);
	}

	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
	
}